package com.xiedafeng.www.util;

import com.xiedafeng.www.exception.ReflectException;
import com.xiedafeng.www.exception.DatabaseException;

import javax.sql.DataSource;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class SearchUtil<T,E,X> {
    /*
    该方法进行单表查询，一个对象或类会对应一个表（如User类就对应User表），t对象就代表这个可能传进来的对象，，condition代表T这个类中有哪些属性（成员变量）
    是要作为限定条件的，由调用dao的service决定，传入变量名即可，（如要按照账号userName查找user就传入“UserName”），具体的条件由t对象确定，这是刚开始写项目时写的，用的是数组，通过getMethod获取相对应的Getter和Setter方法，进行
    sql语句的构建和执行结果的获取，后来写的一些方法基本都用成了Map来构建sql
     */
        public List<T> search(T t, String[] condition)  {
        DataSource dataSource=new DataSourceUtil();
        StringBuilder sql= new StringBuilder("select * from " + t.getClass().getSimpleName());
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        List<T> tList=new ArrayList<>();
        try {
            connection=dataSource.getConnection();
            //connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql.toString());
            //构建sql语句，并使用PreparedStatement赋值
            try {
                if (condition.length > 0) {
                    sql.append(" where ").append(condition[0]).append(" = ?");
                    Method method = t.getClass().getMethod("get" + condition[0]);
                    Object object = null;
                    object = method.invoke(t);
                    preparedStatement = connection.prepareStatement(sql.toString());
                    preparedStatement.setObject(1, object);
                    if (condition.length > 1) {
                        for (int i = 1; i < condition.length; i++) {
                            sql.append(" and ").append(condition[i]).append(" = ?");
                        }
                        preparedStatement = connection.prepareStatement(sql.toString());
                        preparedStatement.setObject(1, object);
                        for (int i = 1; i < condition.length; i++) {
                            method = t.getClass().getMethod("get" + condition[i]);
                            object = method.invoke(t);
                            preparedStatement.setObject(i + 1, object);
                        }
                    }
                }
            }catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e){
                throw  new ReflectException(00001,"通过反射调用对象方法构建sql语句时出现异常");
            }
            resultSet = preparedStatement.executeQuery();
            /*
            使用ResultSetMetaData是构建get或set方法，毕竟变量名首字母小写，去到getter或setter中就成大写了，呜呜呜，为了Java命名规范，
            那就直接在数据库设计时解决大小写转化问题，
            毕竟Windows下的MySQL不区分大小写，这样就不用用Field的啦，也不用考虑大小写转换
            */
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            //获取t所有成员变量，根据数组的大小确定循环次数（即一行有多少列数据）
            Field[] fields = t.getClass().getDeclaredFields();
            int count = fields.length;
            try {
                while (resultSet.next()) {
                    Constructor constructor = t.getClass().getConstructor();
                    T t1 = (T) constructor.newInstance();
                    for (int i = 0; i < count; i++) {
                        Method m = t.getClass().getMethod("set" + resultSetMetaData.getColumnName(i + 2), fields[i].getType());
                        m.invoke(t1, resultSet.getObject(i + 2));
                    }
                    tList.add(t1);
                }
            }catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException | InstantiationException e){
                throw new ReflectException(00002,"通过反射获取sql查询结果时出现异常");
            }
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throw new DatabaseException("数据库连接池回收连接异常");
            }
            JdbcUtil.close(resultSet,preparedStatement);

        }
        return tList;
    }
    /*
    多表查询，t和e是两个不同类的对象，t去寻求关联，e被t关联，一般情况下，关联表表明即两个对应的类的类名，寻求关联的在前，即t，被关联的在后，即e，Map是条件，
    （如发表事件使，t即为event，e即为user，关联表的表名即为eventuser，Map可以是{“UserName”，user。getUserName}）
    但是，像是点赞，收藏等，这些也是将event和user进行关联，于是就有了这第三个泛型X x，它可以是点赞类Thumbup，收藏类Collect，关联表的表名就是对应的类名，x也可以是空null，
    同时，进行多表查询时还要确定service需要哪个表（类或对象）的数据，于是后来又有了position这个参数来指明是要第几个，
    t和e的顺序还是比较重要的！！！
    在举报功能那里，需要将举报的user和举报notice进行关联，而举报notice还需要和接收处理举报的user进行关联，也就是有两个user和notice的关联表，突然感觉像是在写个聊天系统或公告
   一开始想莫非又要像点赞，收藏那里一样在来一个第三方（类）对象，仔细一想，还是可以先确定哪个是主动寻求关联，哪个是被关联，举报时notice是寻求关联，user是被关联（我是把少的一方当作被关联
   ，多的是主动，如一个user可以发出多条notice，user是少的，接收时，一个notice可以发到多个user，这时notice是少的）
     */

    /**
     *
     * @param position
     * @param t
     * @param e
     * @param condition
     * @param x
     * @return
     * @throws Exception
     */
    public List relationSearch(int position, T t, E e, Map condition, X x) throws Exception {
        StringBuilder sql;
        if (x==null) {
            sql = new StringBuilder("select * from " + t.getClass().getSimpleName() + "," + e.getClass().getSimpleName() + "," + t.getClass().getSimpleName() + e.getClass().getSimpleName() +
                    " where " + t.getClass().getSimpleName() + ".id" + " = " +
                    t.getClass().getSimpleName() + e.getClass().getSimpleName() + "." + t.getClass().getSimpleName() + "id" +
                    " and " + e.getClass().getSimpleName() + ".id" + " = " +
                    t.getClass().getSimpleName() + e.getClass().getSimpleName() + "." + e.getClass().getSimpleName() + "id");
        }
        else {
            sql = new StringBuilder("select * from " + t.getClass().getSimpleName() + "," + e.getClass().getSimpleName() + "," + x.getClass().getSimpleName() +
                    " where " + t.getClass().getSimpleName() + ".id" + " = " +
                    x.getClass().getSimpleName() + "." + t.getClass().getSimpleName() + "id" +
                    " and " + e.getClass().getSimpleName() + ".id" + " = " +
                    x.getClass().getSimpleName() + "." + e.getClass().getSimpleName() + "id");
        }
        DataSource dataSource=new DataSourceUtil();
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        List list=new ArrayList<>();
        try {
            connection = dataSource.getConnection();
            Set keySet=condition.keySet();
            for (Object key:keySet) {
                sql.append(" and ").append(key).append(" = ?");
            }
            preparedStatement = connection.prepareStatement(sql.toString());
            int j=1;
            for (Object key:keySet
            ) {
                preparedStatement.setObject(j++,condition.get(key));
            }
            resultSet=preparedStatement.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            Field[] tFields=t.getClass().getDeclaredFields();
            int tCount=tFields.length;
            Field[] eFields=e.getClass().getDeclaredFields();
            int eCount=eFields.length;
            Field[] xfields = new Field[0];
            int xcount=0;
            if (x!=null) {
                xfields = x.getClass().getDeclaredFields();
                xcount = xfields.length;
            }
            while (resultSet.next()){
                if (position==1) {
                    Constructor constructor = t.getClass().getConstructor();
                    T t1 = (T) constructor.newInstance();
                    for (int i = 0; i < tCount; i++) {
                        Method method = t.getClass().getMethod("set" + resultSetMetaData.getColumnName(i + 1+ 1), tFields[i].getType());
                        method.invoke(t1, resultSet.getObject(i + 2));
                    }
                    list.add(t1);
                }
                if (position == 3) {
                    Constructor constructor = x.getClass().getConstructor();
                    X x1 = (X) constructor.newInstance();
                    for (int i = 0; i < xcount; i++) {
                        Method method = x.getClass().getMethod("set" + resultSetMetaData.getColumnName(i + tCount + 1 + eCount + 1 + 3), xfields[i].getType());
                        method.invoke(x1, resultSet.getObject(i + tCount + 1 + eCount + 1 + 3));
                    }
                    list.add(x1);
                }
                if (position == 2) {
                    Constructor constructor = e.getClass().getConstructor();
                    E e1 = (E) constructor.newInstance();
                    for (int i = 0; i < eCount; i++) {
                        Method method = e.getClass().getMethod("set" + resultSetMetaData.getColumnName(i + tCount + 1 + 2), eFields[i].getType());
                        method.invoke(e1, resultSet.getObject(i + tCount + 1 + 2));
                    }
                    list.add(e1);
                }

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            connection.close();
            JdbcUtil.close(resultSet,preparedStatement);
        }
        return list;
    }
    /*
    这个方法是用来查询数据库中数据的主键id的，只要能找到，那返回值都不为0，否则就返回0，常在对关联表插入数据或者删除某一个表某行数据时使用
     */
    public int searchId(T t, Map condition) throws SQLException {
        int id = 0;
        StringBuilder sql= new StringBuilder("select * from " + t.getClass().getSimpleName());
        Object[] key=condition.keySet().toArray();
        sql.append(" where ").append(key[0]).append(" = ?");
        for (int i = 1; i < key.length; i++) {
            sql.append(" and ").append(key[i]).append(" = ?");
        }
        DataSource dataSource=new DataSourceUtil();
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            connection = dataSource.getConnection();
            preparedStatement= connection.prepareStatement(sql.toString());
            for (int i = 0; i < key.length; i++) {
                preparedStatement.setObject(i+1,condition.get(key[i]));
            }
            resultSet=preparedStatement.executeQuery();
            while (resultSet.next()) {
                id=resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            connection.close();
            JdbcUtil.close(resultSet,preparedStatement);
        }
        return id;
    }
}
